﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class DeliveryNotePenaltyDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DeliveryNotePenaltyDAL()
        {
        
        }

        public DeliveryNotePenaltyInfo GetDeliveryNotePenalty(int seq)
        {
            string strSQL;
            DataSet ds;
            DataRowCollection dr;
            DeliveryNotePenaltyInfo info = new DeliveryNotePenaltyInfo();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT dn.*"
                            + ", p.ICM_PO_PEN_NAME "
                            + ", p.ICM_PO_PEN_DESCR "
                            + ", p.ICM_PO_PEN_RATE "
                            + ", p.ICM_PEN_RATE_TYPE_SEQ "
                            + ", p.ICM_PO_PEN_LEAST "
                            + ", p.ICM_PEN_COUNT_SEQ "
                            + ", rt.ICM_PEN_RATE_TYPE_NAME "
                            + ", pc.ICM_PEN_COUNT_NAME "
                        + " FROM ICM_DELIVERY_NOTE_PENALTY dn"
                            + " INNER JOIN ICM_PURCHASE_ORDER_PENALTY p ON dn.ICM_PO_PEN_SEQ = p.ICM_PO_PEN_SEQ"
                            + " INNER JOIN ICM_SYS_PENALTY_RATE_TYPE rt ON p.ICM_PEN_RATE_TYPE_SEQ = rt.ICM_PEN_RATE_TYPE_SEQ"
                            + " INNER JOIN ICM_SYS_PENALTY_COUNT pc ON p.ICM_PEN_COUNT_SEQ = pc.ICM_PEN_COUNT_SEQ"
                        + " WHERE dn.ICM_DN_PEN_SEQ = @seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    SysPenaltyRateTypeInfo infoPenaltyRateType = new SysPenaltyRateTypeInfo();
                    SysPenaltyCountInfo infoPenaltyCount = new SysPenaltyCountInfo();
                    DeliveryNoteInfo infoDn = new DeliveryNoteInfo();
                    PurchaseOrderPenaltyInfo infoPoPenalty = new PurchaseOrderPenaltyInfo();

                    info.Seq = Convert.ToInt32(dr[0]["ICM_DN_PEN_SEQ"]);
                    info.Remark = dr[0]["ICM_DN_PEN_REMARK"].ToString();
                    info.Calculate = (Convert.IsDBNull(dr[0]["PENALTY_CALCULATE"]) ? null : (Nullable<int>)(dr[0]["PENALTY_CALCULATE"]));
                    info.Amount = (Convert.IsDBNull(dr[0]["PENALTY_AMOUNT"]) ? null : (Nullable<double>)(dr[0]["PENALTY_AMOUNT"]));
                    info.FinalAmount = (Convert.IsDBNull(dr[0]["PENALTY_FINAL_AMOUNT"]) ? null : (Nullable<double>)(dr[0]["PENALTY_FINAL_AMOUNT"]));
                    infoDn.Seq = Convert.ToInt32(dr[0]["ICM_DN_SEQ"]);
                    infoPoPenalty.Seq = Convert.ToInt32(dr[0]["ICM_PO_PEN_SEQ"]);
                    infoPoPenalty.Name = dr[0]["ICM_PO_PEN_NAME"].ToString();
                    infoPoPenalty.Descr = dr[0]["ICM_PO_PEN_DESCR"].ToString();
                    infoPoPenalty.Rate = (Convert.IsDBNull(dr[0]["ICM_PO_PEN_RATE"]) ? null : (Nullable<double>)(dr[0]["ICM_PO_PEN_RATE"]));
                    infoPoPenalty.Least = (Convert.IsDBNull(dr[0]["ICM_PO_PEN_LEAST"]) ? null : (Nullable<double>)(dr[0]["ICM_PO_PEN_LEAST"]));
                    infoPenaltyRateType.Seq = Convert.ToInt32(dr[0]["ICM_PEN_RATE_TYPE_SEQ"]);
                    infoPenaltyRateType.Name = dr[0]["ICM_PEN_RATE_TYPE_NAME"].ToString();
                    infoPenaltyCount.Seq = Convert.ToInt32(dr[0]["ICM_PEN_COUNT_SEQ"]);
                    infoPenaltyCount.Name = dr[0]["ICM_PEN_COUNT_NAME"].ToString();

                    infoPoPenalty.InfoPenaltyRateType = infoPenaltyRateType;
                    infoPoPenalty.InfoPenaltyCount = infoPenaltyCount;

                    infoPoPenalty.PenaltyDetail = infoPoPenalty.Rate.ToString() + " " + infoPoPenalty.InfoPenaltyRateType.Name + " ต่อ " + infoPoPenalty.InfoPenaltyCount.Name +
                        ((infoPoPenalty.Least != null) ? "และไม่ต่ำกว่าวันละ " + ((double)infoPoPenalty.Least).ToString("#,##0.00") + " บาท" : "");

                    info.InfoDn = infoDn;
                    info.InfoPoPenalty = infoPoPenalty;
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public IList<DeliveryNotePenaltyInfo> GetDeliveryNotePenaltyWithParent(int dn_seq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<DeliveryNotePenaltyInfo> infoList = new List<DeliveryNotePenaltyInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@dn_seq", dn_seq);

                strSQL = "SELECT np.* "
                            + ", pop.ICM_PO_PEN_NAME "
                            + ", pop.ICM_PO_PEN_DESCR "
                            + ", pop.ICM_PO_PEN_RATE "
                            + ", pop.ICM_PEN_RATE_TYPE_SEQ "
                            + ", pop.ICM_PEN_COUNT_SEQ "
                            + ", pop.ICM_PO_PEN_LEAST "
                            + ", rt.ICM_PEN_RATE_TYPE_NAME "
                            + ", pc.ICM_PEN_COUNT_NAME "
                        + " FROM ICM_DELIVERY_NOTE_PENALTY np"
                            + " INNER JOIN ICM_PURCHASE_ORDER_PENALTY pop ON np.ICM_PO_PEN_SEQ = pop.ICM_PO_PEN_SEQ"
                            + " LEFT JOIN ICM_SYS_PENALTY_RATE_TYPE rt ON pop.ICM_PEN_RATE_TYPE_SEQ = rt.ICM_PEN_RATE_TYPE_SEQ"
                            + " LEFT JOIN ICM_SYS_PENALTY_COUNT pc ON pop.ICM_PEN_COUNT_SEQ = pc.ICM_PEN_COUNT_SEQ"
                        + " where np.ICM_DN_SEQ = @dn_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        DeliveryNotePenaltyInfo info = new DeliveryNotePenaltyInfo();
                        SysPenaltyRateTypeInfo infoPenaltyRateType = new SysPenaltyRateTypeInfo();
                        SysPenaltyCountInfo infoPenaltyCount = new SysPenaltyCountInfo();
                        DeliveryNoteInfo infoDn = new DeliveryNoteInfo();
                        PurchaseOrderPenaltyInfo infoPoPenalty = new PurchaseOrderPenaltyInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_DN_PEN_SEQ"]);
                        info.Remark = dr[i]["ICM_DN_PEN_REMARK"].ToString();
                        info.Calculate = (Convert.IsDBNull(dr[i]["PENALTY_CALCULATE"]) ? null : (Nullable<int>)(dr[i]["PENALTY_CALCULATE"]));
                        info.Amount = (Convert.IsDBNull(dr[i]["PENALTY_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["PENALTY_AMOUNT"]));
                        info.FinalAmount = (Convert.IsDBNull(dr[i]["PENALTY_FINAL_AMOUNT"]) ? null : (Nullable<double>)(dr[i]["PENALTY_FINAL_AMOUNT"]));
                        infoDn.Seq = Convert.ToInt32(dr[i]["ICM_DN_SEQ"]);
                        infoPoPenalty.Seq = Convert.ToInt32(dr[i]["ICM_PO_PEN_SEQ"]);
                        infoPoPenalty.Name = dr[i]["ICM_PO_PEN_NAME"].ToString();
                        infoPoPenalty.Descr = dr[i]["ICM_PO_PEN_DESCR"].ToString();
                        infoPoPenalty.Rate = (Convert.IsDBNull(dr[i]["ICM_PO_PEN_RATE"]) ? null : (Nullable<double>)(dr[i]["ICM_PO_PEN_RATE"]));
                        infoPoPenalty.Least = (Convert.IsDBNull(dr[i]["ICM_PO_PEN_LEAST"]) ? null : (Nullable<double>)(dr[i]["ICM_PO_PEN_LEAST"]));
                        infoPenaltyRateType.Seq = Convert.ToInt32(dr[i]["ICM_PEN_RATE_TYPE_SEQ"]);
                        infoPenaltyRateType.Name = dr[i]["ICM_PEN_RATE_TYPE_NAME"].ToString();
                        infoPenaltyCount.Seq = Convert.ToInt32(dr[i]["ICM_PEN_COUNT_SEQ"]);
                        infoPenaltyCount.Name = dr[i]["ICM_PEN_COUNT_NAME"].ToString();

                        infoPoPenalty.InfoPenaltyRateType = infoPenaltyRateType;
                        infoPoPenalty.InfoPenaltyCount = infoPenaltyCount;

                        infoPoPenalty.PenaltyDetail = infoPoPenalty.Rate.ToString() + " " + infoPoPenalty.InfoPenaltyRateType.Name + " ต่อ " + infoPoPenalty.InfoPenaltyCount.Name +
                            ((infoPoPenalty.Least != null) ? "และไม่ต่ำกว่าวันละ " + ((double)infoPoPenalty.Least).ToString("#,##0.00") + " บาท" : "");

                        info.InfoDn = infoDn;
                        info.InfoPoPenalty = infoPoPenalty;

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Insert(IList<DeliveryNotePenaltyInfo> infoList, Nullable<int> dn_seq, DeliveryNoteInfo info, IDBManager dbManagerTrans)
        {
            string strSQL;
            try
            {
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManagerTrans.CreateParameters(8);

                    dbManagerTrans.AddParameters(0, "@dn_seq", dn_seq);
                    dbManagerTrans.AddParameters(1, "@penalty_seq", infoList[i].InfoPoPenalty.Seq);
                    dbManagerTrans.AddParameters(2, "@remark", infoList[i].Remark);
                    dbManagerTrans.AddParameters(3, "@calculate", infoList[i].Calculate);
                    dbManagerTrans.AddParameters(4, "@amount", infoList[i].Amount);
                    dbManagerTrans.AddParameters(5, "@final_amount", infoList[i].FinalAmount);
                    dbManagerTrans.AddParameters(6, "@created_by", info.InfoRecord.Created_by);
                    dbManagerTrans.AddParameters(7, "@created_when", info.InfoRecord.Created_when);

                    strSQL = "INSERT INTO ICM_DELIVERY_NOTE_PENALTY("
                                + "ICM_DN_SEQ"
                                + ", ICM_PO_PEN_SEQ"
                                + ", ICM_DN_PEN_REMARK"
                                + ", PENALTY_CALCULATE"
                                + ", PENALTY_AMOUNT"
                                + ", PENALTY_FINAL_AMOUNT"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@dn_seq"
                                + ", @penalty_seq"
                                + ", @remark"
                                + ", @calculate"
                                + ", @amount"
                                + ", @final_amount"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                    dbManagerTrans.ExecuteNonQuery(CommandType.Text, strSQL);
                }
            }
            catch
            {
                throw;
            }
        }

        public void Insert(DeliveryNotePenaltyInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@penalty_seq", info.InfoPoPenalty.Seq);
                dbManager.AddParameters(1, "@remark", info.Remark);
                dbManager.AddParameters(2, "@calculate", info.Calculate);
                dbManager.AddParameters(3, "@amount", info.Amount);
                dbManager.AddParameters(4, "@final_amount", info.FinalAmount);
                dbManager.AddParameters(5, "@dn_seq", info.InfoDn.Seq);
                dbManager.AddParameters(6, "@created_by", info.InfoRecord.Created_by);
                dbManager.AddParameters(7, "@created_when", info.InfoRecord.Created_when);

                strSQL = "INSERT INTO ICM_DELIVERY_NOTE_PENALTY("
                                + "ICM_DN_SEQ"
                                + ", ICM_PO_PEN_SEQ"
                                + ", ICM_DN_PEN_REMARK"
                                + ", PENALTY_CALCULATE"
                                + ", PENALTY_AMOUNT"
                                + ", PENALTY_FINAL_AMOUNT"
                                + ", CREATED_BY"
                                + ", CREATED_WHEN"
                                + ") "
                            + " VALUES (@dn_seq"
                                + ", @penalty_seq"
                                + ", @remark"
                                + ", @calculate"
                                + ", @amount"
                                + ", @final_amount"
                                + ", @created_by"
                                + ", @created_when"
                                + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(DeliveryNotePenaltyInfo info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", info.Seq);
                dbManager.AddParameters(1, "@penalty_seq", info.InfoPoPenalty.Seq);
                dbManager.AddParameters(2, "@remark", info.Remark);
                dbManager.AddParameters(3, "@calculate", info.Calculate);
                dbManager.AddParameters(4, "@amount", info.Amount);
                dbManager.AddParameters(5, "@final_amount", info.FinalAmount);
                dbManager.AddParameters(6, "@updated_by", info.InfoRecord.Updated_by);
                dbManager.AddParameters(7, "@updated_when", info.InfoRecord.Updated_when);

                strSQL = "UPDATE ICM_DELIVERY_NOTE_PENALTY"
                    + " SET ICM_PO_PEN_SEQ = @penalty_seq"
                    + ", ICM_DN_PEN_REMARK = @remark"
                    + ", PENALTY_CALCULATE = @calculate"
                    + ", PENALTY_AMOUNT = @amount"
                    + ", PENALTY_FINAL_AMOUNT = @final_amount"
                    + ", UPDATED_BY = @updated_by"
                    + ", UPDATED_WHEN = @updated_when"
                 + " WHERE ICM_DN_PEN_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteFromDB(IList<DeliveryNotePenaltyInfo> infoList)
        {
            string strSQL;
            string seq = "";
            for (int i = 0; i < infoList.Count; i++)
            {
                if (i == 0)
                {
                    seq += infoList[i].Seq;
                }
                else
                {
                    seq += "," + infoList[i].Seq;
                }
            }

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();

                strSQL = "DELETE FROM ICM_DELIVERY_NOTE_PENALTY"
                       + " WHERE ICM_DN_PEN_SEQ IN (" + seq + ")";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
